package sdfi.lee.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import sdfi.lee.Entity.Character;
import sdfi.lee.Entity.Rollnotice;
import sdfi.lee.Entity.Rolltype;

public class RolltypeDAO {
		public List<Rolltype> getRolltype()
		{
			List<Rolltype> list=new ArrayList<Rolltype>();
			Session session=DAO.openSession();
			Transaction ts=null;
			try
			{
				ts=session.beginTransaction();
				String sql="from Rolltype as a where a.id>=1 and a.id<=5";
				Query query=session.createQuery(sql);
				list=query.list();
				ts.commit();
			}
			catch(Exception ee)
			{
				System.out.println("Exception when getRollType");
				ee.printStackTrace();
				ts.rollback();
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		/*
		public List<Rolltype> getRolltype()
		{
			List<Rolltype> list=new ArrayList<Rolltype>();
			Session session=DAO.openSession();
			Transaction ts=null;
			try
			{
				ts=session.beginTransaction();
				String sql="from Rolltype";
				Query query=session.createQuery(sql);
				list=query.list();
				ts.commit();
			}
			catch(Exception ee)
			{
				System.out.println("Exception when getRollType");
				ee.printStackTrace();
				ts.rollback();
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}*/
		public List<Rollnotice> getRollnotice(int pg,int size,int type)
		{
			Session session=DAO.openSession();
			List<Rollnotice> list=new ArrayList<Rollnotice>();
			Transaction ts=session.beginTransaction();
			try
			{
				String sql="";
				if(type==0)
					sql="select new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
						"from Rollnotice as a inner join a.rolltype as b order by a.rolldate desc";
				else
					sql="select new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
"from Rollnotice as a inner join a.rolltype as b where b.id="+type+" order by a.rolldate desc";
			
				Query query=session.createQuery(sql);
				query.setFirstResult((pg-1)*size);
				query.setMaxResults(size);
				
				list=query.list();
				ts.commit();
			}
			catch(Exception ee)
			{
				ts.rollback();
				ee.printStackTrace();
				System.out.println("Exception ee when get rollnotice");
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		public List<Rollnotice> getRollnotice(int pg,int size,int type,String keyword)
		{
			Session session=DAO.openSession();
			List<Rollnotice> list=new ArrayList<Rollnotice>();
			Transaction ts=session.beginTransaction();
			try
			{
				String sql="";
				if(type==0)
					sql="select new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
						"from Rollnotice as a inner join a.rolltype as b where a.rolltitle like '%"+keyword+"%'";
				else
					sql="select new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
"from Rollnotice as a inner join a.rolltype as b where b.id="+type+" and a.rolltitle like :kw";
				
				sql+=" order by a.rolldate desc";
				Query query=session.createQuery(sql);
				query.setParameter("kw", "%"+keyword+"%");
				query.setFirstResult((pg-1)*size);
				query.setMaxResults(size);
				list=query.list();
				ts.commit();
			}
			catch(Exception ee)
			{
				ts.rollback();
				ee.printStackTrace();
				System.out.println("Exception ee when get rollnotice");
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		public String deleteCharacter(Integer id) throws Exception
		{
			Session session=DAO.openSession();
			Connection con=session.connection();
			con.setAutoCommit(false);
			try
			{
				Statement st=con.createStatement();
				String sql="delete from mycharacter where id='"+id+"'";
				int n=st.executeUpdate(sql);
				con.commit();
				if(n<=0) return "error";
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
				con.rollback();	
				return "error";
			}
			return "success";
		}
		public List<Rollnotice> getRollnotice(int pg,int size,int type,Integer userid)
		{
			Session session=DAO.openSession();
			List<Rollnotice> list=new ArrayList<Rollnotice>();
			Connection con=session.connection();
			try
			{
				String sql="select a.id id, a.rollpath rollpath,a.rolldate rolldate, a.rolltitle from rollnotice as a,mycharacter as b" +
						" where a.rolltype=b.rolltype and a.rolltitle like concat('%',b.keyword,'%') and b.userid="+userid+" and a.rolltype="+type+" order by a.rolldate desc";
		
				ResultSet rs=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery(sql);
				
				int all=(pg-1)*size;
				for(int i=0;i<all;i++)
				{
					if(!rs.next()) return list;
				}
				
					Set<String> set=new HashSet<String>();
 					for(int i=0;i<size;i++)
					{
						if(rs.next())
						{
							
							//if(!set.contains(rs.getString("rolltitle"))) {
								Rollnotice roll=new Rollnotice();
								roll.setRollpath(rs.getString("rollpath"));
								roll.setRolltitle(rs.getString("rolltitle"));
								roll.setRolldate(rs.getDate("rolldate"));
								set.add(rs.getString("rolltitle"));
								list.add(roll);
							//}
							
						
						}
					}
				
				rs.close();
				con.commit();
			
				
			}
			catch(Exception ee)
			{
				try
				{
					con.rollback();
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
				
			}
			finally
			{
				try
				{
					con.close();
					DAO.closeSession();
				
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
			}
			
			return list;
		}
		public List<Character> getUserCharacter(Integer userid)
		{
			List<Character> list=new ArrayList<Character>();
			Session session=DAO.openSession();
			Transaction ts=session.beginTransaction();
			try
			{
				String sql="select new sdfi.lee.Entity.Character(a.rolltype,a.keyword) from Character as a where a.userid="+userid+" group by a.rolltype";
				Query query=session.createQuery(sql);
				
				list=query.list();
				
				ts.commit();
			}
			catch(Exception ee)
			{
				ee.printStackTrace();
				ts.rollback();
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		public List<Rollnotice> getRollnotice(int pg,int size,String rtype)
		{
			Session session=DAO.openSession();
			List<Rollnotice> list=new ArrayList<Rollnotice>();
			Connection con=session.connection();
			try
			{
				String 	sql="select *from  rollnotice as a where a.rolltype="+rtype+" limit "+(pg-1)*size+","+pg*size;
				ResultSet rs=con.createStatement().executeQuery(sql);
				java.text.SimpleDateFormat sdf=new java.text.SimpleDateFormat("yyyy-MM-dd");
				while(rs.next())
				{
					Rollnotice notice=new Rollnotice();
					notice.setRolldate(rs.getDate("rolldate"));
					notice.setRolltitle(rs.getString("rolltitle"));
					notice.setRollpath(rs.getString("rollpath"));
					notice.setId(rs.getInt("id"));
					list.add(notice);
				}
			}
			catch(Exception ee)
			{
				
				ee.printStackTrace();
				System.out.println("Exception ee when get rollnotice");
			}
			finally
			{
				DAO.closeSession();
			}
			return list;
		}
		
	
		public void  getRollnotice(List<Rollnotice>list,int pg,int size,int type,Integer userid)
		{
			Session session=DAO.openSession();
		
			Connection con=session.connection();
			try
			{
				String sql="select  a.id id, a.rollpath rollpath,a.rolldate rolldate, a.rolltitle from rollnotice as a,mycharacter as b" +
						" where a.rolltype=b.rolltype and a.rolltitle like concat('%',b.keyword,'%') and b.userid="+userid+" and a.rolltype="+type+" order by a.rolldate desc";
				
				ResultSet rs=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery(sql);
				int all=(pg-1)*size;
				for(int i=0;i<all;i++)
				{
					if(!rs.next()) return ;
				}
				
					HashSet<String> set=new HashSet<String>();
					
					for(int i=0;i<size;i++)
					{
						if(rs.next())
						{
							

							//if(!set.contains(rs.getString("rolltitle"))) {
								Rollnotice roll=new Rollnotice();
								roll.setRollpath(rs.getString("rollpath"));
								roll.setRolltitle(rs.getString("rolltitle"));
								roll.setRolldate(rs.getDate("rolldate"));
								set.add(rs.getString("rolltitle"));
								list.add(roll);
							//}
					
						}
					}
				
				
				rs.close();
				con.commit();
			
				
			}
			catch(Exception ee)
			{
				try
				{
					con.rollback();
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
				
			}
			finally
			{
				try
				{
					con.close();
					DAO.closeSession();
				
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
			}
		
			
		}
		
		
		public void getRollnotice(List<Rollnotice> list,int pg,int size,int type)
		{
			Session session=DAO.openSession();
			
			Transaction ts=session.beginTransaction();
			try
			{
				String sql="";
				if(type==0)
					sql="select  new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
						"from Rollnotice as a inner join a.rolltype as b order by a.rolldate desc";
				else
					sql="select new sdfi.lee.Entity.Rollnotice(a.rollpath,a.rolldate,a.rolltitle) " +
"from Rollnotice as a inner join a.rolltype as b where b.id="+type+" order by a.rolldate desc";
			
				Query query=session.createQuery(sql);
				query.setFirstResult((pg-1)*size);
				query.setMaxResults(size);
				List nlist=query.list();
				if(nlist.size()>0)
					for(int i=0;i<nlist.size();i++)
				list.add((Rollnotice)query.list().get(i));
				
				ts.commit();
			}
			catch(Exception ee)
			{
				ts.rollback();
				ee.printStackTrace();
				System.out.println("Exception ee when get rollnotice");
			}
			finally
			{
				DAO.closeSession();
			}
			
		}
		
		public String deleteRollnotice(String id)
		{
			Session session=DAO.openSession();
			
			Connection con=session.connection();
			try
			{
				String sql="delete from rollnotice where id="+id+"";
				con.setAutoCommit(false);
				int n=con.createStatement().executeUpdate(sql);
				con.commit();
				if(n>0) return "success";
				else return "error"	;
			}
			catch(Exception ee)
			{
				try
				{
					con.rollback();
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
				return "error";
				
			}
			finally
			{
				try
				{
					con.close();
					DAO.closeSession();
				
				}
				catch(Exception eee)
				{
					eee.printStackTrace();
				}
			}
		
			
		}
}
